﻿/*
 * Created by SharpDevelop.
 * User: BadaninMV
 * Date: 04.03.2015
 * Time: 10:44
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Net.Mime;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using Region;
using Region.Utils;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
namespace Region.Forms
{
  /// <summary>
  /// Description of Import.
  /// </summary>
  public partial class Import : Form
  {
    Engine m_engine;
    List<Dictionary<String,Object>> ListImport;
    TableView.ColumnCollection.Column cellInNalog;
    TableView.ColumnCollection.Column cellInRegion;
    TableView.ColumnCollection.Column cellOGRN;
    TableView.ColumnCollection.Column cellINN;
    TableView.ColumnCollection.Column cellRegion;
    TableView.ColumnCollection.Column cellRaion;
    TableView.ColumnCollection.Column cellGorod;
    TableView.ColumnCollection.Column cellNaspunkt;
    TableView.ColumnCollection.Column cellStreet;
    TableView.ColumnCollection.Column cellDom;
    TableView.ColumnCollection.Column cellKorpus;
    TableView.ColumnCollection.Column cellOffice;
    TableView.ColumnCollection.Column cellGosReg;
    String[] poles = new string[]{
        "INN",
        "OGRN",
        "KontRegion",
        "KontRaion",
        "KontGorod",
        "KontNaspunkt",
        "KontStreet",
        "KontDom",
        "KontKorpus",
        "KontKvart",
        "GosReg",
        "InNalog",
        "InRegion",
        "NalogOKOPF",
        "KontName",
        "NalogKontNameFirm",
    "NalogOkvedMain","NalogOkved","NalogRegDate","NalogKillDate"};
    public Import(Engine engime)
    {
      m_engine = engime;
      //
      // The InitializeComponent() call is required for Windows Forms designer support.
      //
      InitializeComponent();
      
      //
      // TODO: Add constructor code after the InitializeComponent() call.
      //
      cellInNalog = tableView1.Columns.Add("InNalog","Налоговая",1);
      cellInRegion = tableView1.Columns.Add("InRegion","Регион",2);
      cellOGRN = tableView1.Columns.Add("OGRN","ОГРН",5);
      cellINN = tableView1.Columns.Add("INN","ИНН",6);
      /*cellRegion = tableView1.Columns.Add("KontRegion","Регион",10);
      cellRaion = tableView1.Columns.Add("KontRaion","Район",11);
      cellGorod = tableView1.Columns.Add("KontGorod","Город",12);
      cellNaspunkt = tableView1.Columns.Add("KontNaspunkt","Нас.пункт",13);
      cellStreet = tableView1.Columns.Add("KontStreet","Улица",14);
      cellDom = tableView1.Columns.Add("KontDom","Дом",15);
      cellKorpus = tableView1.Columns.Add("KontKorpus","Корпус",16);
      cellOffice = tableView1.Columns.Add("KontKvart","Офис",17);
      cellGosReg = tableView1.Columns.Add("GosReg","Гос. регистрация",20);
      tableView1.Columns.Add("NalogOKOPF","NalogOKOPF",30);
      tableView1.Columns.Add("NalogKontNameFirm","NalogKontNameFirm",40);
      */
      /*
            line.KontName = KontName.Text;
            line.INN = INN.Text;
            line.OGRN = OGRN.Text;
            line.Region = RegionEdit.Text;
            line.Raion = Raion.Text;
            line.Gorod = Gorod.Text;
            line.Naspunkt = Naspunkt.Text;
            line.Street = Street.Text;
            line.Dom = Dom.Text;
            line.Korpus = Korpus.Text;
            line.Office = Office.Text;
            line.GosReg = GosReg.Value;
             */
    }
    void ShowList()
    {
      tableView1.Rows.Clear();
      
      for(Int32 i=0;i<ListImport.Count;i++)
      {
        var row = tableView1.Rows.newRow();
        foreach(String pole in poles)    
        {
          var cell = row.getCell(pole);
          if (cell!=null)cell.Value = ListImport[i][pole];
        }
      }
      
    }
    public static void getInfoNalog(Dictionary<String, Object> ki, SqlConnection NalogDB,String OGRN)
    {

         SQLResult resUL = Engine.SQLSelect( @"
SELECT [UL_OPF].[NAME] AS [OKOPF],
  [NAMES],
  [UL_START].[DTREG] AS [DTSTART],
  [UL_FINISH].[DTREG] AS [DTFINISH]
FROM [UL_UL]
  INNER JOIN [UL_NAME] ON [UL_NAME].[IDUL]=[UL_UL].[IDUL]
  INNER JOIN [UL_OPF] ON [UL_OPF].[IDOPF]=UL_NAME.[IDOPF]
  INNER JOIN [UL_START] ON [UL_START].[IDUL]=[UL_UL].[IDUL]
  LEFT JOIN [UL_FINISH] ON [UL_FINISH].[IDUL]=[UL_UL].[IDUL]
WHERE [UL_UL].[OGRN]=@OGRN
",new SQLParam(){{"@OGRN", OGRN}},NalogDB);
        if (resUL.Count>0)
        {
          ki["NalogOKOPF"] = (String)resUL[0]["OKOPF"];
          ki["NalogKontNameFirm"] = (String)resUL[0]["NAMES"];
          ki["NalogRegDate"] = (RegionDate)resUL[0]["DTSTART"];
          ki["NalogKillDate"] = (RegionDate)resUL[0]["DTFINISH"];
        }
         SQLResult resIP = Engine.SQLSelect( @"
SELECT [FL_MNS].[DTSTART],[FL_MNS].[DTEND],[FAM_FL],[NAME_FL],[OTCH_FL]
FROM [FL_IP]
  INNER JOIN [FL_FL] ON [FL_FL].[IDFL]=[FL_IP].[IDFL]
  INNER JOIN [FL_MNS] ON [FL_MNS].[IDIP]=[FL_FL].[IDIP]
WHERE [FL_IP].[OGRNIP]=@OGRN
",new SQLParam(){{"@OGRN", OGRN}},NalogDB);
        if (resIP.Count>0)
        {
          ki["NalogOKOPF"] = "Индивидуальный предприниматель";
          String Name = (String)resIP[0]["NAME_FL"];
          String OTCH = (String)resIP[0]["OTCH_FL"];
          ki["NalogKontNameFirm"] = "ИП "+(String)resIP[0]["FAM_FL"]+" ";
          if (Name.Length>0) ki["NalogKontNameFirm"] += Name[0]+".";
          if (OTCH.Length>0) ki["NalogKontNameFirm"] += OTCH[0]+".";
          ki["NalogKontNameFirm"]=((String)ki["NalogKontNameFirm"]).Trim();
          ki["NalogRegDate"] = (RegionDate)resIP[0]["DTSTART"];
          ki["NalogKillDate"] = (RegionDate)resIP[0]["DTEND"];
        }            
        SQLResult resOkved = Engine.SQLSelect( @"
SELECT [KOD_OKVED],[MAIN]
FROM [UL_UL]
INNER JOIN [UL_OKVED] ON [UL_OKVED].[IDUL]=[UL_UL].[IDUL]
WHERE [UL_UL].[OGRN]=@OGRN
",new SQLParam(){{"@OGRN", OGRN}},NalogDB);
        ki["NalogOkved"]="";
        ki["NalogOkvedMain"]="";
        for(Int32 i=0;i<resOkved.Count;i++)
        {
          if ((String)resOkved[i]["KOD_OKVED"]=="1")
            ki["NalogOkvedMain"] = (String)resOkved[i]["KOD_OKVED"];
          else
            ki["NalogOkved"]=((String)ki["NalogOkved"])+((String)resOkved[i]["KOD_OKVED"]);
        }        
SQLResult resOkvedIP = Engine.SQLSelect( @"
SELECT [KOD_OKVED],[MAIN]
FROM [FL_IP]
INNER JOIN [FL_OKVED] ON [FL_OKVED].[IDIP]=[FL_IP].[IDIP]
WHERE [FL_IP].[OGRNIP]=@OGRN
",new SQLParam(){{"@OGRN", OGRN}},NalogDB);
        for(Int32 i=0;i<resOkvedIP.Count;i++)
        {
          if ((String)resOkvedIP[i]["KOD_OKVED"]=="1")
            ki["NalogOkvedMain"] = (String)resOkvedIP[i]["KOD_OKVED"];
          else
            ki["NalogOkved"]=((String)ki["NalogOkved"])+((String)resOkvedIP[i]["KOD_OKVED"])+"; ";
        }        


    }    
    void ToolStripButton1Click(object sender, EventArgs e)
    {
      
      var rgx = new Regex(@"[0-9]{10,15}", RegexOptions.IgnoreCase);
      var list = rgx.Matches(textBox1.Text);
      var OGRNList = new List<string>();      
      ListImport = new List<Dictionary<string, object>>();     
      var NalogDB = new SqlConnection(m_engine.config.getValue<String>("EGRIPBD",""));
      NalogDB.Open();      
      foreach(Match l in list)
      {
        var item = new Dictionary<string, object>();        
        
        foreach(String pole in poles) 
          item[pole]="";
        //item["InNalog"] = false;
        //Ю
        var P = new Dictionary<String, Object>();
        if (l.Value.Length==10 || l.Value.Length==12)
        {
          item["INN"]=l.Value;
          P["INN"] = item["INN"];
        }
        if (l.Value.Length==13 || l.Value.Length==15)
        {
          item["OGRN"] = l.Value;
          P["OGRN"] = item["OGRN"];
        }
        if (l.Value.Length==11 || l.Value.Length==14) continue;
        List<KontList.EGRLine> ListEGR = KontList.getListEGRUL(m_engine, P);
        
        item["NalogOKOPF"] = "";
        foreach(KontList.EGRLine line in ListEGR)
        {
          //item["InNalog"] = true;
          item["INN"] = line.INN;
          item["OGRN"] = line.OGRN;
          
          item["KontName"] = line.KontName;
          
          item["KontRegion"] = line.Region;
          item["KontRaion"] = line.Raion;
          item["KontGorod"] = line.Gorod;
          item["KontNaspunkt"] = line.Naspunkt;
          item["KontStreet"] = line.Street;
          item["KontDom"] = line.Dom;
          item["KontKorpus"] = line.Korpus;
          item["KontKvart"] = line.Office;
          item["KontGosReg"] = line.GosReg;
          getInfoNalog(item, NalogDB, (String)item["OGRN"]);
          
          
        }
        
        ListImport.Add(item);        
      }
      ShowList();
    }
    void ToolStripButton2Click(object sender, EventArgs e)
    {
      var fd = new SaveFileDialog();
      fd.ShowDialog();        
      //%RegionN%	%Num%	%OKOPF%	%KontName%	%KontNameFirm%	%Okved%	%OkvedExt%	%Region%	%Raion%	%Nasp%	%Address%			%OGRN%	%INN%			%RegDate%		%KillDate%	%KontStart%	%CheckPlan%		%Otdel%	%TO%	%NumPlan%	%NumVP%	%NumDef%
      var EL = new List<Dictionary<String,Object>>();
      var EL2 = new List<Dictionary<String,Object>>();
      Int32 Num = 1;
      foreach(var imp in ListImport)
      {
          var ExportLine = new Dictionary<String,Object>();
          ExportLine["%RegionN%"] = "27";
          ExportLine["%Num%"] = Num++;
          ExportLine["%OKOPF%"] = imp["NalogOKOPF"];
          ExportLine["%KontName%"] = imp["KontName"];
          ExportLine["%KontNameFirm%"] = imp["NalogKontNameFirm"];

          ExportLine["%Otdel%"] = "-";//ot[k.OtdelID].OtdelName;
          ExportLine["%TO%"] = "-";//ot[ot[k.OtdelID].OID].OtdelName;

          //var Olist = (from o in k.Okved orderby o.Value descending select o.Key).ToList();
          ExportLine["%Okved%"] = imp["NalogOkvedMain"];//Olist.Count>0?Olist[0]:"";

          //ExportLine["%Okved%"] = k.NalogOkvedMain;
          ExportLine["%OkvedExt%"] = imp["NalogOkved"];// String.Join(", ",k.NalogOkved.ToArray());
          ExportLine["%Region%"] = imp["KontRegion"];
          ExportLine["%Raion%"] = Engine.getAddressString("","",(String)imp["KontRaion"],(String)imp["KontGorod"],"","","","","");
          ExportLine["%Nasp%"] = Engine.getAddressString("","","",(String)imp["KontGorod"],(String)imp["KontNaspunkt"],"","","","");
          ExportLine["%Address%"] = Engine.getAddressString("","","","","",(String)imp["KontStreet"],(String)imp["KontDom"],(String)imp["KontKorpus"],(String)imp["KontKvart"]);
          ExportLine["%INN%"] = imp["INN"];
          ExportLine["%OGRN%"] = imp["OGRN"];
          ExportLine["%RegDate%"] = imp["NalogRegDate"];
          ExportLine["%KillDate%"] = imp["NalogKillDate"];
          ExportLine["%CheckPlan%"] = "";//imp["PlanCheck"];
          ExportLine["%KontStart%"] = "";//imp["KontStart"];
          ExportLine["%NumPlan%"] = 0;
          ExportLine["%NumVP%"] = 0;
          ExportLine["%NumDef%"] = 0;
          EL.Add(ExportLine);
      }
      var Template = m_engine.getRegionFile("Pilot","xlsx");
      var Poles = new Dictionary<String,Object>();
      var Tables = new Dictionary<String,List<Dictionary<String,Object>>>(){{"%RegionN%",EL},{"%Num%",EL2}};
      Export.export("xlsx",Template,fd.FileName,Poles,Tables);

    }
  }
}
